package com.itheima.leyou.dao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.text.NumberFormat;
import java.util.*;

@Repository
public class StorageDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public ArrayList<Map<String, Object>> getStorageQuanty(String sku_id){
        String where = "";
        if (!sku_id.equals("")){
            where = " WHERE sku_id = "+sku_id;
        }else {
            where = " WHERE 1=0 ";
        }
        String sql = "SELECT sku_id, quanty FROM tb_stock_storage " +where;
        return (ArrayList<Map<String,Object>>) jdbcTemplate.queryForList(sql);
    }

    public Map<String, Object> insertStorage(Integer warehouse_id, String sku_id, double inquanty, double outquanty){
        String sql = "SELECT id FROM tb_stock_storage WHERE warehouse_id = " + warehouse_id + " AND sku_id = " + sku_id;

        Map<String, Object> map = new HashMap<>();
        map = jdbcTemplate.queryForMap(sql);

        int new_id = 0;
        boolean result = false;
        Map<String, Object> resultMap = new HashMap<>();

        if (map.isEmpty()){
            double quanty = inquanty-outquanty;
            sql = "INSERT INTO tb_stock_storage (warehouse_id, sku_id, quanty) VALUES ("+warehouse_id+", "+sku_id+", "+quanty+")";

            SqlParameterSource namedParameters = new MapSqlParameterSource();
            KeyHolder keyHolder = new GeneratedKeyHolder();
            result = jdbcTemplate.update(sql, namedParameters, keyHolder)==1;

            if (!result){
                resultMap.put("result", false);
                resultMap.put("msg", "写入库存主表失败！");
                return resultMap;
            }

            new_id = keyHolder.getKey().intValue();
        }else {
            Object o = map.get("id");
            if (o==null||o.equals("")){
                resultMap.put("result", false);
                resultMap.put("msg", "获取库存主表编号失败！");
                return resultMap;
            }else {
                new_id = Integer.parseInt(o.toString());
            }
        }

        sql = "INSERT INTO tb_stock_storage_history (stock_storage_id, in_quanty, out_quanty) VALUES ("+new_id+", "+inquanty+", "+outquanty+")";
        result = jdbcTemplate.update(sql)==1;

        if (!result){
            resultMap.put("result", false);
            resultMap.put("msg", "写入库存明细表失败！");
            return resultMap;
        }

        sql = "UPDATE tb_stock_storage SET quanty = quanty + "+inquanty+" - "+outquanty+" " +
                "WHERE id = "+new_id + " AND quanty +"+inquanty+" - "+outquanty+" >= 0";
        result = jdbcTemplate.update(sql)==1;

        if (result){
            resultMap.put("result", true);
            resultMap.put("msg", "");
        }else{
            resultMap.put("result", false);
            resultMap.put("msg", "最后更新剩余数量失败！");
        }
        return resultMap;
    }
}
